<?php
/**
 ***********************************************************************************************************************
 * mysql监控工具: 可监控最大连接数异常
 * @author ysp
 ***********************************************************************************************************************
 */
namespace artisan\db;
use artisan\cache;
use artisan\log;
use artisan\queue;
use artisan\config;
use artisan\db;

/**
 * Class DBMonitorUtil
 * @package artisan\db
 */
class DBMonitorUtil
{
    /**是否追踪最大连接数
     */
    public static $enableMaxCnsTrace=false;


//    public static $debugMaxCnxDbNames=['feedback','ivr','sms'];
    public static $debugMaxCnxDbNames=[];



    /**追踪最大连接数预警值，高于这个值时会记录日志预警
     * 如追踪db_message实例:['feedback','ivr','sms'];
     * @var boolean
     */
    public static $maxConnectCounts=5;

    /**紧急情况根据mysql host屏蔽sql,正常情况下不要打开
     * @var string
     */
    private static  $FilterSqlByHosts=[
//        'rm-bp1wa754025p24645.mysql.rds.aliyuncs.com'=>'base',//$base
//        'rm-bp1br2y84g23738kv.mysql.rds.aliyuncs.com'=>'express',//$express
//        'rm-bp1lk98otu9a9q1g3.mysql.rds.aliyuncs.com'=>'log',//$log
//        'rm-bp19145k6wdh2t0b6.mysql.rds.aliyuncs.com'=>'message',//$message
//        'rm-bp19776gelh53i88e.mysql.rds.aliyuncs.com'=>'pda',//$pda
//        'rm-bp19566cfm8i7k2ss.mysql.rds.aliyuncs.com'=>'finance',//$finance
//        'rm-bp10104e905uuf3z4.mysql.rds.aliyuncs.com'=>'core',//$core
//        'rm-bp12s2vt4ji58ji8h.mysql.rds.aliyuncs.com'=>'address'//$address
    ];

    /**紧急情况根据mysql语句关键字屏蔽sql,正常情况下不要打开
     * @var string
     */
     private static $FilterSqlByKeyStrs=[
//         'parsed_address',
//         ' tbl_shop_counterman_online ',
    ];


    /* *是否拦截当前sql执行
    */
    public static function checkSqlIsOk($dbHost,$sql){
        /*$orderstable = ['card_stock','order_continue','tbl_branch_mobiles','tbl_cainiao_auth','tbl_card_batch','tbl_card_details','tbl_courier_waybill','tbl_elesingle_account','tbl_elesingle_apply','tbl_elesingle_waybills','tbl_eshop','tbl_ess_apply','tbl_ess_apply_copy','tbl_ess_apply_copy1','tbl_ess_apply_copy2','tbl_ess_courier','tbl_ess_courier_copy1','tbl_ess_operation','tbl_ess_order','tbl_ess_recycling','tbl_ess_setting','tbl_ess_trading_error','tbl_esszt_account','tbl_esszt_apply','tbl_esszt_courier','tbl_esszt_trading_error','tbl_goods','tbl_goods_courier','tbl_inform_sender','tbl_order','tbl_order_bak_2017_5','tbl_order_continue','tbl_order_deal_log','tbl_order_grab','tbl_order_hurry','tbl_order_ivr','tbl_order_ivr_bak','tbl_order_ivr_disabled','tbl_order_message','tbl_order_no','tbl_order_operate','tbl_order_pay','tbl_order_remind','tbl_order_resource','tbl_order_send_record','tbl_order_user','tbl_order_vas','tbl_order_waybill','tbl_order_zto_id','tbl_order_zto_relation','tbl_orders','tbl_orders_ws','tbl_ordersms_err_notice','tbl_realname_continue','tbl_realname_id_card','tbl_realname_push_record','tbl_realname_relation','tbl_realname_renling_sms','tbl_realname_send_arrive','tbl_realname_source','tbl_realname_statistic_courier','tbl_realname_statistic_shop','tbl_refuse_order_failed','tbl_scan_tmp','tbl_shop_realname_line','tbl_thermalpaper_ocr','tbl_thermalpaper_order','tbl_zb_couriers_statistics','tbl_zb_task','tbl_zb_task_orders','tbl_zb_task_push_message','tbl_zb_user_couriers','tbl_zb_wechat_push_message','tbl_zbshop','tbl_zbshop_admin','tbl_zbshop_admin_online','tbl_zbshop_copy','tbl_zbshop_couriers','tbl_zbshop_online','tbl_zto2o_count','tbl_zto2o_customer','tbl_zto2o_import','tbl_zto2o_shop'];
        $ac = new \soa\ACPreg\ACPreg();
        if(strpos(strtolower($sql),'join') !== false && $ac->findWordsInArray($orderstable,strtolower($sql))){
            logDebug($sql,'find_ishave_orders_tables','join',true);
        }

        //匹配table1,table2联表查询
        preg_match('/from ([a-zA-Z0-9_]*\s*,.*) where/',$sql,$matches);
        if(!empty($matches[1]) && $ac->findWordsInArray($orderstable,strtolower($matches[1]))){
            logDebug($sql,'find_ishave_orders_tables','preg_match',true);
        }

        unset($ac);*/

        if(isset(self::$FilterSqlByHosts[$dbHost])){
            //logDebug('全实例拦截('.$dbHost.'):'.self::$FilterSqlByHosts[$dbHost].'实例，跳过sql执行','FilterSqlByHosts');
            return false;//跳过该sql,不执行
        }
        foreach(self::$FilterSqlByKeyStrs as $eachSqlStr){
            if(trim($eachSqlStr)&&strpos($sql,$eachSqlStr)!==false){
                //logDebug('Sql拦截('.$dbHost.')当前执行:'.$sql,'FilterSqlByKeyStrs');
                return false;//如果sql包含该关键字，跳过不执行
            }
        }
        return true;
    }


    /* 监控最大连接数start
    * @return array
    */
    public static function getDebugMaxCnxHosts(){
        /**
         * 如追踪db_message线上实例:'rm-bp19145k6wdh2t0b6.mysql.rds.aliyuncs.com'
         * 注意:$debugMaxCnxHost,$debugMaxCnxDbNames是两种不同策略，任选一个配置就行了,不要两者都配置,系统优先取$debugMaxCnxHost判断
         */
        $dbConfInfos=config::get('db');
        
        return [
//            $dbConfInfos['express']['host'],//express实例(取express库配置项)
            $dbConfInfos['users']['host'],//base实例(取users库配置项)
            $dbConfInfos['kd_shop']['host'],//core实例(取kd_shop库配置项)
            $dbConfInfos['sms']['host'],//message实例(取sms库配置项)
//            $dbConfInfos['kd_pda']['host'],//pda实例(取kd_pda库配置项)
//            $dbConfInfos['kd_address']['host'],//address实例(取kd_address库配置项)
//            $dbConfInfos['dts']['host'],//log实例(取dts库配置项)
//            $dbConfInfos['finance']['host'],//finance实例(取finance库配置项)
        ];
    }



    /* 监控最大连接数start*/
    public static function monitorConnectCountsStart($mysqli,$dbHost,$dbUserName,$dbName,$uuid=''){
        if(!self::$enableMaxCnsTrace){
            return;
        }
        //$cacheKey=$dbHost.'_'.$dbName;
        $cacheKey=$dbHost;
        $uuid=''.$mysqli->thread_id;
        $debugMaxCnxHosts=self::getDebugMaxCnxHosts();
        if(($debugMaxCnxHosts&&in_array($dbHost,$debugMaxCnxHosts))||(self::$debugMaxCnxDbNames&&in_array($dbName,self::$debugMaxCnxDbNames))) {
            //记录mysql连接数详情
            $debugInfo = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
            if (count($debugInfo) > 5) {
                array_splice($debugInfo, 0, 5);
                if (count($debugInfo) > 3) {
                    array_splice($debugInfo, count($debugInfo) - 3, 3);
                }
            }
            //记录当前PHP程序信息
            $mysqlDebugInfo = [
                'db' => $dbName,
                'threadId'=>$mysqli->thread_id,
                'date'=>date('Y-m-d H:i:s', time()),
                'url' => (isset($_SERVER) && isset($_SERVER['HTTP_HOST']) && isset($_SERVER['REQUEST_URI'])) ? 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'] : '',
                'params'=>$_REQUEST,
                'trace' => $debugInfo
            ];
            $mysqlDebugInfo=json_encode($mysqlDebugInfo, JSON_UNESCAPED_UNICODE);
            $isOk=cache::connect('log')->hset($cacheKey,$uuid,$mysqlDebugInfo);
            if(false===$isOk){
                logDebug('mysql连接信息记录失败: cacheKey:'.$cacheKey.' uuid:'.$uuid.' ' . $mysqlDebugInfo, 'msg_cons_err');
            }else{
                //logDebug('mysql连接信息记录成功: cacheKey:'.$cacheKey.' uuid:'.$uuid.' ' . $mysqlDebugInfo, 'msg_cons_err');
            }
            /**************************************************************************************end**/
        }
    }

    /* 监控最大连接数end*/
    public static function monitorConnectCountsEnd($mysqli,$dbHost,$dbName,$uuid='')
    {
        if(!self::$enableMaxCnsTrace){
            return;
        }
        //$cacheKey=$dbHost.'_'.$dbName;
        $cacheKey=$dbHost;
        $uuid=''.$mysqli->thread_id;
        $debugMaxCnxHosts=self::getDebugMaxCnxHosts();
        //如果调试连接数，缓存计数-1
        if(($debugMaxCnxHosts&&in_array($dbHost,$debugMaxCnxHosts))||(self::$debugMaxCnxDbNames&&in_array($dbName,self::$debugMaxCnxDbNames))){
            //判断连接是否已关闭
            //if(!cache::connect('log')->isConnectionClosed()){
                $isOk=cache::connect('log')->hDel($cacheKey,$uuid);
                if(false===$isOk){
                    logDebug('mysql连接信息删除失败: cacheKey:'.$cacheKey.' uuid:'.$uuid.' ', 'msg_cons_err');
                }else{
                    //logDebug('mysql连接信息删除成功: cacheKey:'.$cacheKey.' uuid:'.$uuid.' ', 'msg_cons_suc');
                }
            //}
        }
    }


    public static function fetchArray($resource = null)
    {
        $result = [];
        if(empty($resource)) {
            return $result;
        }
        while ($row = mysqli_fetch_assoc($resource)) {
            $result[] = $row;
        }
        return $result;
    }

    /*
     * 将从sql中获取的表名放入redis 集合中
     */
    public static function setToRedis($sql){
    //    return [];//临时屏蔽
        if (empty($sql) || !is_string($sql)) {
            raise(1000, 'sql语句为空或非字符串');
        }
        if($sql =='SHOW TABLES'){
//            logDebug($sql,'sql');
            return false;
        }
        $tableNameResult = self::getTableName($sql);
        if(empty($tableNameResult) || !is_array($tableNameResult)){
//            logDebug('NO RESULT SQL__'.$sql,'getTableResult');
            return false;
        }
        $config = config::get('del_table_name');
        $name = $config['table_name'];

        $dbName = 'allDbNameInSet';
        $key = date('Y_m_d',time()).'_'.'tableName';              //收集每天所用表名的键；
        $allTableNameKey ='getAllTableName';                      //从开始记录到目前时间的键名
        foreach ($tableNameResult as $value){
            $table = trim($value);
            if(in_array($table, $name)){                          //屏蔽已经删除的表
                continue;
            }

            if(!cache::connect('log')->exists($dbName)){ continue;}    //判断redis中此键名是否存在；不存在就跳过

            if(cache::connect('log')->sIsMember($dbName,$table)){
                $keyNum        =cache::connect('log')->zScore($key,$table);                           //看表名是否存在；返回的是表名的使用次数,不存在返回false;存在返回次数；
                $allNameKeyNum =cache::connect('log')->zScore($allTableNameKey, $table);

                $keyNum === false ? $keyNum =1 : $keyNum += 1;                             //每天的键名对应的值进行判断
                $allNameKeyNum === false ? $allNameKeyNum = 1 : $allNameKeyNum += 1;       // 长时间键名对应的值进行判断

                cache::connect('log')->zAdd($key,$keyNum,$table);
                cache::connect('log')->zAdd($allTableNameKey,$allNameKeyNum,$table);         //此键名用于收集长时间使用的表名；
            }else{
                $result = $table.'__'.$sql;
//                logDebug($result,'tableNameNotToRedis','kdhelp',true);
            }
        }
       return true;
    }
     /*
     * 通过sql语句获取表名
     * @return string
     */
    public static function getTableName($sql)
    {
        if (empty($sql) || !is_string($sql)) {
            raise(1000, 'sql语句为空或非字符串');
        }
        $temp = [];
        $newSql = strtolower($sql);                                    //将sql转换为小写  线上的表名不允许有大写；
        $sqlStr = preg_replace('/on\s+duplicate\s+key\s+update\s+/',' ',$newSql);
        $sqlRet = preg_replace('/(\s+)/', '|', $sqlStr);               //此位置将空格替换为 |; 避免多个空格出现影响结果
        $sqlArr = explode('|', $sqlRet);                               //分隔为数组;
        foreach ($sqlArr as $key => $value) {
            if ($value === 'from'  || $value === 'join' || $value === 'into' || $value === 'update') {                           // || $value === 'like'
                if ($sqlArr[$key + 1] !== '(select' && $sqlArr[$key + 1] !== '(SELECT' && $sqlArr[$key + 1] !== '(' && $sqlArr[$key+1] !== 'like' && $sqlArr[$key+1] !== 'LIKE') {           //去除子查询和建表的sql
                    $tmp = $sqlArr[$key + 1];
                    $pattern = array('/\(.*/', '/\).*/', '/\`/', '/\'/', '/\"/','/.*%$/','/^%.*%$/','/^%.*/');  //过滤左单括号，右单括号，斜引号 ，单引号，双引号，以%结尾的的like模糊查询的；
                    $res = preg_replace($pattern,'', $tmp);
                    if(!empty($res)){
                        $temp[] = $res;
                    }
                }
            }
        }
        unset($sqlArr);
        if(empty($temp)){
            $temp = $sql;
        }
        return $temp;
     }
}